﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
	public class RoleOperationProvider : PhotoGallery.Core.Providers.DALProvider.IRoleOperationProvider
	{
		#region Sql
		private static readonly string SqlSelectRoleOperation = "SELECT [OperationId],[RoleId], [Status],[AddDate] FROM [RoleOperation] WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;";

		private static readonly string SqlInsertRoleOperation = "INSERT INTO [RoleOperation]([OperationId],[RoleId], [Status]) values(@OperationId,@RoleId, @Statuse);";

		private static readonly string SqlUpdateRoleOperation = "UPDATE [RoleOperation] SET [Status] = @Status,[AddDate] = @AddDate WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;";

		private static readonly string SqlDeleteRoleOperation = "DELETE [RoleOperation] WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;";

		private static readonly string SqlGetAllRoleOperation = "SELECT [OperationId],[RoleId], [Status],[AddDate] FROM [RoleOperation];";

		#endregion

#region Procedures
/*

-- Begin of RoleOperation
IF OBJECT_ID(N'[USP_RoleOperation_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_RoleOperation_Delete_By_Id]

IF OBJECT_ID(N'[USP_RoleOperation_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_RoleOperation_Select_By_Id]

IF OBJECT_ID(N'[USP_RoleOperation_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_RoleOperation_Insert]

IF OBJECT_ID(N'[USP_RoleOperation_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_RoleOperation_Update_By_Id]

IF OBJECT_ID(N'[USP_RoleOperation_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_RoleOperation_SelectAll]

GO

--region [USP_RoleOperation_Delete_By_Id]
CREATE PROCEDURE [USP_RoleOperation_Delete_By_Id]
	@OperationId	int,
	@RoleId	int
AS
SET NOCOUNT ON
DELETE [RoleOperation] WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;
--endregion
GO

--region [USP_RoleOperation_Select_By_Id]
CREATE PROCEDURE [USP_RoleOperation_Select_By_Id]
	@OperationId	int,
	@RoleId	int
AS
SET NOCOUNT ON
SELECT [OperationId],[RoleId], [Status],[AddDate] FROM [RoleOperation] WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;
--endregion
GO

--region [USP_RoleOperation_Insert]
CREATE PROCEDURE [USP_RoleOperation_Insert]
	@OperationId	int,
	@RoleId	int,
	@Status	int,
	@AddDate	datetime
AS
SET NOCOUNT ON
INSERT INTO [RoleOperation]([OperationId],[RoleId], [Status],[AddDate]) values(@OperationId,@RoleId, @Status,@AddDate);
--endregion
GO

--region [USP_RoleOperation_Update_By_Id]
CREATE PROCEDURE [USP_RoleOperation_Update_By_Id]
	@Status	int,
	@AddDate	datetime,
	@OperationId	int,
	@RoleId	int
AS
SET NOCOUNT ON
UPDATE [RoleOperation] SET [Status] = @Status,[AddDate] = @AddDate WHERE  [OperationId]=@OperationId AND [RoleId]=@RoleId ;
--endregion
GO

--region [USP_RoleOperation_SelectAll]
CREATE PROCEDURE [USP_RoleOperation_SelectAll]
AS
SET NOCOUNT ON
SELECT [OperationId],[RoleId], [Status],[AddDate] FROM [RoleOperation];
--endregion
GO


-- End of RoleOperation
*/
#endregion

		#region IRoleOperationProvider Members
		public Core.Business.RoleOperation Select(RoleOperationKey id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@OperationId", SqlDbType.Int, id.OperationId);
			sql.AddParameter("@RoleId", SqlDbType.Int, id.RoleId);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectRoleOperation);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.RoleOperation roleOperation = new Core.Business.RoleOperation();

				roleOperation.Id = new Core.Business.RoleOperationKey();
				if (!reader.IsDBNull(0)) roleOperation.Id.OperationId = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) roleOperation.Id.RoleId = reader.GetInt32(1);
				if (!reader.IsDBNull(2)) roleOperation.Status = reader.GetInt32(2);
				if (!reader.IsDBNull(3)) roleOperation.AddDate = reader.GetDateTime(3);

				reader.Close();
				return roleOperation;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.RoleOperation roleOperation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Status", SqlDbType.Int, roleOperation.Status);
			//sql.AddParameter("@AddDate", SqlDbType.DateTime, roleOperation.AddDate);
			sql.AddParameter("@OperationId", SqlDbType.Int, roleOperation.Id.OperationId);
			sql.AddParameter("@RoleId", SqlDbType.Int, roleOperation.Id.RoleId);
			sql.ExecuteSql(SqlInsertRoleOperation);
		}

		public void Update(Core.Business.RoleOperation roleOperation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Status", SqlDbType.Int, roleOperation.Status);
			sql.AddParameter("@AddDate", SqlDbType.DateTime, roleOperation.AddDate);
			sql.AddParameter("@OperationId", SqlDbType.Int, roleOperation.Id.OperationId);
			sql.AddParameter("@RoleId", SqlDbType.Int, roleOperation.Id.RoleId);
			sql.ExecuteSql(SqlUpdateRoleOperation);
		}

		public void Delete(Core.Business.RoleOperation roleOperation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@OperationId", SqlDbType.Int, roleOperation.Id.OperationId);
			sql.AddParameter("@RoleId", SqlDbType.Int, roleOperation.Id.RoleId);
			sql.ExecuteSql(SqlDeleteRoleOperation);
		}

		public IList<Core.Business.RoleOperation> GetAllRoleOperation()
		{
			IList<Core.Business.RoleOperation> roleOperationlist = new List<Core.Business.RoleOperation>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllRoleOperation);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.RoleOperation roleOperation = new Core.Business.RoleOperation();

					roleOperation.Id = new Core.Business.RoleOperationKey();
					if (!reader.IsDBNull(0)) roleOperation.Id.OperationId = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) roleOperation.Id.RoleId = reader.GetInt32(1);
					if (!reader.IsDBNull(2)) roleOperation.Status = reader.GetInt32(2);
					if (!reader.IsDBNull(3)) roleOperation.AddDate = reader.GetDateTime(3);

					roleOperation.MarkOld();
					roleOperationlist.Add(roleOperation);
				}
				reader.Close();
			}
			return roleOperationlist;
		}
		#endregion
	}
}
